﻿Imports MySql.Data.MySqlClient
Imports System.Data.SqlClient
Imports System.Text

Module modeMySQL
    Dim conn As MySqlConnection

    ''' <summary>
    ''' 事件状态
    ''' </summary>
    ''' <remarks></remarks>
    Public Enum EventState As Short
        ''' <summary>启用</summary>
        stateEnable = 0
        ''' <summary>禁用</summary>
        stateDisible = 1
        ''' <summary>从机禁用</summary>
        stateDisibleOnSlave = 2
    End Enum
    ''' <summary>
    ''' 事件周期单位枚举
    ''' </summary>
    ''' <remarks></remarks>
    Public Enum EveryUnit As Short
        '''<summary>年</summary>
        UNIT_YEAR = 0
        '''<summary>季度</summary>
        UNIT_QUARTER = 1
        '''<summary>月</summary>
        UNIT_MONTH = 2
        '''<summary>天</summary>
        UNIT_DAY = 3
        '''<summary>小时</summary>
        UNIT_HOUR = 4
        '''<summary>分钟</summary>
        UNIT_MINUTE = 5
        '''<summary>星期</summary>
        UNIT_WEEK = 6
        '''<summary>秒</summary>
        UNIT_SECOND = 7
        '''<summary>年月</summary>
        UNIT_YEAR_MONTH = 8
        '''<summary>天小时</summary>
        UNIT_DAY_HOUR = 9
        '''<summary>天分钟</summary>
        UNIT_DAY_MINUTE = 10
        '''<summary>天秒数</summary>
        UNIT_DAY_SECOND = 11
        '''<summary>小时分钟</summary>
        UNIT_HOUR_MINUTE = 12
        '''<summary>小时秒数</summary>
        UNIT_HOUR_SECOND = 13
        '''<summary>分秒</summary>
        UNIT_MINUTE_SECOND = 14
    End Enum
    ''' <summary>
    ''' 事件类型枚举
    ''' </summary>
    ''' <remarks></remarks>
    Enum EventType As Short
        TYPE_INSERT = 0
        TYPE_UPDATE = 1
        TYPE_DELETE = 2
    End Enum
    ''' <summary>
    ''' 删除类型
    ''' </summary>
    ''' <remarks></remarks>
    Enum DropType As Short
        ''' <summary> 删除表单</summary>
        DROP_TABLE = 0
        ''' <summary> 删除事件</summary>
        DROP_EVENT = 1
        ''' <summary> 删除触发器</summary>
        DROP_TRIGGER = 2
        ''' <summary>删除存储过程</summary>
        DROP_PROCEDURE = 3
        ''' <summary>删除一个现存的函数</summary>
        DROP_FUNCTION = 4
        ''' <summary>删除视图</summary>
        DROP_VIEW = 5
        ''' <summary>删除数据库</summary>
        DROP_DATABASE = 6
    End Enum
    Enum EngineType As Short
        [InnoDB] = 0
        [Archive] = 1
        [MEMORY] = 2
        [CSV] = 3
        [BLACKHOLE] = 4
        [MyISAM] = 5
        [MRG_MYISAM] = 6
        [PERFORMANCE_SCHEMA] = 7
    End Enum
    ' Dim dataAdapter As MySqlDataAdapter
    ''' <summary>
    ''' 连接到MySql服务端指定的数据库
    ''' </summary>
    ''' <param name="ServerName">服务端名称或地址</param>
    ''' <param name="User">用户名</param>
    ''' <param name="Pwd">密码</param>
    ''' <param name="Port">端口号</param>
    ''' <param name="database">数据库名称</param>
    ''' <returns>成功返回ADODB.Connection已连接的数据对象，失败返回Exception错误对象</returns>
    ''' <remarks></remarks>
    Public Function ConnOpen(Optional ByVal ServerName As String = "localhost", Optional ByVal User As String = "root", Optional ByVal Pwd As String = "", _
                             Optional ByVal Port As Integer = 3306, Optional ByVal database As String = "") As Object
        Dim connSql As String = "server=" & ServerName & ";User=" & User & ";database=" & database & ";Port=" & Port & ";Password=" & Pwd '定义连接字符串
        Try
            conn = New MySqlConnection(connSql)
            conn.Open()
            Return conn
        Catch ex As MySqlException
            Return ex
        End Try
    End Function
    ''' <summary>
    ''' 关闭连接
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub ConnClose()
        If Not conn Is Nothing Then
            If conn.State = 1 Then
                conn.Close()
                conn = Nothing
            End If
        End If
    End Sub
    ''' <summary>
    ''' MySQL事务创建或修改
    ''' </summary>
    ''' <param name="myConn">已建立连接的MySqlConnection</param>
    ''' <param name="EventName">事件名称</param>
    ''' <param name="BeginCode">要执行的SQL代码</param>
    ''' <param name="IsCreate">否创建事件（true 创建事件，false 修改事件）</param>
    ''' <param name="Comment">注释</param>
    ''' <param name="DeleteEvent">事件结束后删除事件</param>
    ''' <param name="eState">事件状态</param>
    ''' <param name="RunOnce">只执行一次（true 执行一次；false 按条件执行）</param>
    ''' <param name="EverySumer">事件执行周期</param>
    ''' <param name="eUnit">事件执行周期单位</param>
    ''' <param name="StartsDate">事件执行的开始时间（如果RunOnce=true，该参数必填）</param>
    ''' <param name="EndsDate"> 事件执行的结束时间（RunOnce=false，如果没有表示一直按周期性执行，永不停止）</param>
    ''' <returns>成功 true，失败输出错误描述</returns>
    ''' <remarks></remarks>
    Public Function MySqlEvent(ByVal myConn As MySqlConnection, ByVal EventName As String, Optional ByVal BeginCode As String = Nothing, Optional ByVal IsCreate As Boolean = True, _
                           Optional ByVal Comment As String = Nothing, Optional ByVal DeleteEvent As Boolean = True, Optional ByVal eState As EventState = EventState.stateEnable, _
                           Optional ByVal RunOnce As Boolean = False, Optional ByVal EverySumer As Integer = 1, Optional ByVal eUnit As EveryUnit = EveryUnit.UNIT_DAY, _
                           Optional ByVal StartsDate As Date = Nothing, Optional ByVal EndsDate As Date = Nothing) As Object
        Try
            '------------------------------------------------
            Dim SqlStr As String
            Dim Schedule As String   '计划时间表
            Dim state As String      '事件状态
            Dim Unit As String
            Select Case eState
                Case EventState.stateEnable
                    state = "ENABLE" & vbCrLf & "  "
                Case EventState.stateDisible
                    state = "DISABLE" & vbCrLf & "  "
                Case EventState.stateDisibleOnSlave
                    state = "DISABLE ON SLAVE" & vbCrLf & "  "
                Case Else
                    state = "ENABLE" & vbCrLf & "  "
            End Select

            Select Case eUnit
                Case EveryUnit.UNIT_DAY
                    Unit = " DAY "
                Case EveryUnit.UNIT_DAY_HOUR
                    Unit = " DAY_HOUR "
                Case EveryUnit.UNIT_DAY_MINUTE
                    Unit = " DAY_MINUTE "
                Case EveryUnit.UNIT_DAY_SECOND
                    Unit = " DAY_SECOND "
                Case EveryUnit.UNIT_HOUR
                    Unit = " HOUR "
                Case EveryUnit.UNIT_HOUR_MINUTE
                    Unit = " HOUR_MINUTE "
                Case EveryUnit.UNIT_HOUR_SECOND
                    Unit = " HOUR_SECOND "
                Case EveryUnit.UNIT_MINUTE
                    Unit = " MINUTE "
                Case EveryUnit.UNIT_QUARTER
                    Unit = " QUARTER "
                Case EveryUnit.UNIT_SECOND
                    Unit = " SECOND "
                Case EveryUnit.UNIT_WEEK
                    Unit = " WEEK "
                Case EveryUnit.UNIT_YEAR
                    Unit = " YEAR "
                Case EveryUnit.UNIT_YEAR_MONTH
                    Unit = " YEAR_MONTH "
                Case Else
                    Unit = " DAY "
            End Select

            If RunOnce Then
                If StartsDate = Nothing Then
                    Schedule = " AT '" & Format(Now, "YYYY-MM-DD HH:mm:ss") & "'" & vbCrLf & "  "
                Else
                    Schedule = " AT '" & Format(StartsDate, "YYYY-MM-DD HH:mm:ss") & "'" & vbCrLf & "  "
                End If
            Else
                Schedule = " EVERY " & EverySumer & Unit
                If StartsDate <> Nothing Then
                    Schedule = Schedule & " STARTS '" & Format$(StartsDate, "YYYY-MM-DD HH:mm:ss") & "'" ' IIf(StartsDate = 0, Format$(Now, "YYYY-MM-DD HH:mm:ss"), Format$(StartsDate, "YYYY-MM-DD HH:mm:ss")) & "'"
                End If
                If EndsDate <> Nothing Then
                    Schedule = Schedule & "ENDS '" & Format$(EndsDate, "YYYY-MM-DD HH:mm:ss") & "'" & vbCrLf & "  "
                Else
                    Schedule = Schedule & vbCrLf & "  "
                End If
            End If
            If IsCreate Then SqlStr = "CREATE EVENT " Else SqlStr = "ALTER EVENT "
            SqlStr = SqlStr & "`" & EventName & "`" & vbCrLf & "      ON SCHEDULE" & vbCrLf & "  " & Schedule
            SqlStr = SqlStr & IIf(DeleteEvent, "ON COMPLETION NOT PRESERVE", "ON COMPLETION PRESERVE") & vbCrLf & "  " & state
            SqlStr = SqlStr & "COMMENT '" & Comment & "'" & vbCrLf & "  DO BEGIN" & vbCrLf & BeginCode & vbCrLf & "END"
            'conn.Execute SqlStr
            Dim oC As MySqlCommand = New MySqlCommand(SqlStr, myConn)
            oC.CommandType = CommandType.Text
            oC.ExecuteNonQuery()
            'oC.Cancel()
            Return True
            '------------------------------------------------
        Catch ex As MySqlException
            Return ex
        End Try
    End Function
    ''' <summary>
    ''' SQL删除
    ''' </summary>
    ''' <param name="myConn">已建立连接的MySqlConnection</param>
    ''' <param name="Name">删除名称</param>
    ''' <param name="dType">删除类型</param>
    ''' <returns>成功 true，失败输出错误描述</returns>
    ''' <remarks></remarks>
    Public Function MySqlDrop(ByVal myConn As MySqlConnection, ByVal Name As String, Optional ByVal dType As DropType = 0) As Object
        Try
            '------------------------------------------------
            Dim SqlStr As String = ""
            Select Case dType
                Case DropType.DROP_TABLE
                    SqlStr = "DROP TABLE `" & Name & "`"
                Case DropType.DROP_EVENT
                    SqlStr = "DROP EVENT `" & Name & "`"
                Case DropType.DROP_PROCEDURE
                    SqlStr = "DROP PROCEDURE `" & Name & "`"
                Case DropType.DROP_FUNCTION
                    SqlStr = "DROP FUNCTION `" & Name & "`"
                Case DropType.DROP_TRIGGER
                    SqlStr = "DROP TRIGGER `" & Name & "`"
                Case DropType.DROP_VIEW
                    SqlStr = "DROP VIEW `" & Name & "`"
                Case DropType.DROP_DATABASE
                    SqlStr = "DROP DATABASE `" & Name & "`"
            End Select
            Dim oC As MySqlCommand = New MySqlCommand(SqlStr, myConn)
            oC.CommandType = CommandType.Text
            oC.ExecuteNonQuery()
            'oC.Cancel()
            Return True
            '------------------------------------------------
        Catch ex As MySqlException
            Return ex
        End Try
    End Function
    ''' <summary>
    ''' MySQL触发器创建
    ''' </summary>
    ''' <param name="myConn">已建立连接的MySqlConnection</param>
    ''' <param name="TriggerName">触发器名称</param>
    ''' <param name="TabName">表单名称（只是要在执行触发的表单）</param>
    ''' <param name="BeginCode">要执行的SQL代码</param>
    ''' <param name="Before">否在指定事件执行之前触发（false 指定事件执行后触发）</param>
    ''' <param name="EType">事件类型</param>
    ''' <returns>成功 true，失败输出错误描述</returns>
    ''' <remarks></remarks>
    Public Function CreateTrigger(ByVal myConn As MySqlConnection, ByVal TriggerName As String, ByVal TabName As String, _
                  Optional ByVal BeginCode As String = "", Optional ByVal Before As Boolean = True, Optional ByVal EType As EventType = EventType.TYPE_INSERT)
        Try
            '------------------------------------------------
            Dim SqlStr As String
            Dim sType As String
            Select Case EType
                Case EventType.TYPE_INSERT
                    sType = "INSERT ON `"
                Case EventType.TYPE_UPDATE
                    sType = "UPDATE ON `"
                Case EventType.TYPE_DELETE
                    sType = "DELETE ON `"
                Case Else
                    sType = "INSERT ON `"
            End Select
            SqlStr = "CREATE TRIGGER `" & TriggerName & IIf(Before, "` BEFORE ", "` AFTER ") & sType & _
                     TabName & "` FOR EACH ROW BEGIN" & vbCrLf & BeginCode & vbCrLf & "END"
            Dim oC As MySqlCommand = New MySqlCommand(SqlStr, myConn)
            oC.CommandType = CommandType.Text
            oC.ExecuteNonQuery()
            'oC.Cancel()
            Return True
        Catch ex As MySqlException
            Return ex
        End Try
    End Function
    ''' <summary>
    ''' 获取数据库路径
    ''' </summary>
    ''' <param name="myConn">已建立连接的MySqlConnection</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetDatabasePath(ByVal myConn As MySqlConnection) As Object
        Try
            Dim dataReader As MySqlDataReader
            Dim oC As MySqlCommand = New MySqlCommand("show variables like '%dir%'", myConn)
            oC.CommandType = CommandType.Text
            dataReader = oC.ExecuteReader()
            If dataReader.HasRows Then
                dataReader.Read()
                Return dataReader.Item("value")
            Else
                Return Nothing
            End If
            dataReader.Close()
        Catch ex As MySqlException
            Return ex
        End Try
    End Function

    '获取服务器中全部数据库名称
    Public Function GetAllDatabase(ByVal myConn As MySqlConnection) As Object
        Try
            Dim DBName() As String = Nothing
            Dim i As Integer
            Dim sqlstr As String = "SHOW DATABASES"
            Dim dataReader As MySqlDataReader
            Dim oC As MySqlCommand = New MySqlCommand(sqlstr, myConn)
            oC.CommandType = CommandType.Text
            dataReader = oC.ExecuteReader()
            If dataReader.HasRows Then
                Do While dataReader.Read()   '一次读取一行
                    ReDim Preserve DBName(i)
                    DBName(i) = dataReader.Item(0)
                    i += 1
                Loop
            End If
            dataReader.Close()
            Return DBName
        Catch ex As MySqlException
            Return ex
        End Try
    End Function
    '返回当前数据库的所有表单
    Function GetDBTabes(ByVal myConn As MySqlConnection, DatabaseName As String) As Object
        Try
            Dim DBTabes() As String = Nothing
            Dim dataReader As MySqlDataReader
            Dim oC As MySqlCommand = New MySqlCommand("show tables from", myConn)
            oC.CommandType = CommandType.Text
            dataReader = oC.ExecuteReader()
            Dim i As Long
            If dataReader.HasRows Then
                Do While dataReader.Read()   '一次读取一行
                    ReDim Preserve DBTabes(i)
                    DBTabes(i) = dataReader.Item(0)
                    i += 1
                Loop
                dataReader.Close()
            End If
            Return DBTabes
        Catch ex As MySqlException
            Return ex
        End Try
    End Function
    '删除数据库
    Function DellDatabase(ByVal myConn As MySqlConnection, ByVal DatabaseName As String) As Object
        Try
            Dim oC As MySqlCommand = New MySqlCommand("DROP DATABASE " & DatabaseName, myConn)
            oC.CommandType = CommandType.Text
            oC.ExecuteNonQuery()
            'oC.Cancel()
            Return True
        Catch ex As MySqlException
            Return ex
        End Try
    End Function
    '创建数据库
    Function CreateDatabase(ByVal myConn As MySqlConnection, ByVal DatabaseName As String) As Object
        Try
            Dim oC As MySqlCommand = New MySqlCommand("CREATE DATABASE IF NOT EXISTS " & DatabaseName, myConn)
            oC.CommandType = CommandType.Text
            oC.ExecuteNonQuery()
            'oC.Cancel()
            Return True
        Catch ex As MySqlException
            Return ex
        End Try
    End Function
    ''' <summary>
    ''' 备份MySQL数据库(含存储过程的备份)
    ''' </summary>
    ''' <param name="MysqldumpEXEPath">
    ''' Mysqldump.exe文件路径（不包含可执行文件本身）。
    ''' 注：如果配置了 Mysqldump.exe环境变量时可设置为空
    ''' </param>
    ''' <param name="serverUrl">服务器地址</param>
    ''' <param name="User">登陆服务器的名称</param>
    ''' <param name="Pwd">登陆服务器使用的密码</param>
    ''' <param name="DatabaseName">需要备份的数据库</param>
    ''' <param name="BackupName">备份文件路径（含文件名）</param>
    ''' <param name="TabName">需要备份的表单名称列表（不填表示所有表）</param>
    ''' <returns>成功返回true，错误返回Exception对象</returns>
    ''' <remarks></remarks>
    Public Function BackupDatabase(ByVal MysqldumpEXEPath As String, ByVal serverUrl As String, ByVal User As String, ByVal Pwd As String, _
                               ByVal DatabaseName As String, ByVal BackupName As String, ParamArray TabName() As Object) As Object
        Try
            '------------------------------------------------
            Dim TabNames As String
            Dim MySqlCmd As String
            TabNames = Join(TabName, " ")        '将变体型数组转换为表单名称字符串
            If LCase$(Right$(BackupName, 4)) <> ".sql" Then BackupName = BackupName & ".sql"
            If Right$(MysqldumpEXEPath, 1) <> "\" Then MysqldumpEXEPath = MysqldumpEXEPath & "\"
            serverUrl = " -h" & serverUrl
            User = " -u" & User
            Pwd = " -p" & Pwd
            DatabaseName = " " & DatabaseName
            If Len(TabNames) > 0 Then TabNames = " " & TabNames
            'mysqldump -u用户名 -p密码 -h主机 数据库名 -d > 路径
            'mysqldump -uroot -p123 --default-character-set=gbk --opt --extended-insert=false --triggers -R --hex-blob -x db_name > f:\db.sql
            'Running: mysqldump.exe --defaults-file="c:\users\admini~1\appdata\local\temp\tmpkgviik.cnf"  --user=root --host=localhost --protocol=tcp --port=3306 --default-character-set=utf8 --single-transaction=TRUE --skip-triggers "invoicing"
            MySqlCmd = "cmd /c " & MysqldumpEXEPath & "mysqldump" & serverUrl & User & Pwd & _
                       " --default-character-set=utf8 --opt --extended-insert=false --triggers -R --hex-blob -x" & _
                       DatabaseName & TabNames & ">" & BackupName

            Call Shell(MySqlCmd, vbHide)  '使用Shell函数调用CMD后台执行mysqldump命令
            '------------------------------------------------
            Return True
        Catch ex As MySqlException
            Return ex
        End Try
    End Function
    ''' <summary>
    ''' 备份MySQL数据库(含存储过程的备份)
    ''' </summary>
    ''' <param name="MysqldumpEXEPath">
    ''' Mysqldump.exe文件路径（不包含可执行文件本身）。
    ''' 注：如果配置了 Mysqldump.exe环境变量时可设置为空
    ''' </param>
    ''' <param name="serverUrl">服务器地址</param>
    ''' <param name="User">登陆服务器的名称</param>
    ''' <param name="Pwd">登陆服务器使用的密码</param>
    ''' <param name="DatabaseName">需要备份的数据库</param>
    ''' <param name="BackupName">备份文件路径（含文件名）</param>
    ''' <param name="TabName">需要备份的表单名称列表（不填表示所有表）</param>
    ''' <returns>成功返回true，错误返回Exception对象</returns>
    ''' <remarks></remarks>
    Public Function BackupDatabaseEx(ByVal MysqldumpEXEPath As String, ByVal serverUrl As String, ByVal User As String, ByVal Pwd As String, _
                                 ByVal DatabaseName As String, ByVal BackupName As String, ParamArray TabName() As Object) As Object
        Try
            '------------------------------------------------
            ' Dim IsNullArray As Boolean
            Dim TabNames As String
            Dim MySqlCmd As String
            TabNames = Join(TabName, " ")        '将变体型数组转换为表单名称字符串
            If LCase$(Right$(BackupName, 4)) <> ".sql" Then BackupName = BackupName & ".sql"
            If Right$(MysqldumpEXEPath, 1) <> "\" Then MysqldumpEXEPath = MysqldumpEXEPath & "\"
            serverUrl = " -h" & serverUrl
            User = " -u" & User
            Pwd = " -p" & Pwd
            DatabaseName = " -d " & DatabaseName
            If Len(TabNames) > 0 Then TabNames = " " & TabNames
            'mysqldump -u用户名 -p密码 -h主机 数据库名 -d > 路径
            MySqlCmd = "cmd /c " & MysqldumpEXEPath & "mysqldump" & serverUrl & User & Pwd & " -d " & DatabaseName & TabNames & "-r>" & BackupName
            Call Shell(MySqlCmd, vbHide)  '使用Shell函数调用CMD后台执行mysqldump命令
            Return True
        Catch ex As MySqlException
            Return ex
        End Try
    End Function
    ''' <summary>
    ''' 还原数据库
    ''' </summary>
    ''' <param name="MysqlEXEPath">
    ''' Mysqldump.exe文件路径（不包含可执行文件本身）。
    ''' 注：如果配置了 Mysql.exe环境变量时可设置为空
    ''' </param>
    ''' <param name="serverUrl">服务器地址</param>
    ''' <param name="User">登陆服务器的名称</param>
    ''' <param name="Pwd">登陆服务器使用的密码</param>
    ''' <param name="DatabaseName">需要还原的数据库</param>
    ''' <param name="BackupName">备份文件路径（含文件名）</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ReductionDatabase(ByVal MysqlEXEPath As String, ByVal serverUrl As String, ByVal User As String, ByVal Pwd As String, _
                                  ByVal DatabaseName As String, ByVal BackupName As String) As Object
        'Running: mysql.exe --defaults-file="c:\users\admini~1\appdata\local\temp\tmp8xdicw.cnf"  --protocol=tcp --host=localhost --user=root --port=3306 --default-character-set=utf8 --comments --database=invoicing  < "G:\\WeX5\\系统工程\\backuptest.sql"
        Try
            Dim MySqlCmd As String
            serverUrl = " -h" & serverUrl
            User = " -u" & User
            Pwd = " -p" & Pwd
            If Right$(MysqlEXEPath, 1) <> "\" Then MysqlEXEPath = MysqlEXEPath & "\"
            DatabaseName = " " & DatabaseName
            '例子：mysql -u用户名 -h主机 -p密码 要恢复的数据库<数据库备份文件路径及文件名
            MySqlCmd = "cmd /c " & MysqlEXEPath & "mysql" & serverUrl & User & Pwd & DatabaseName & "<" & BackupName
            Call Shell(MySqlCmd, vbHide)  '使用Shell函数调用CMD后台执行mysqldump命令
            Return True
        Catch ex As MySqlException
            Return ex
        End Try
    End Function
    ''' <summary>
    ''' 备份数据库表单
    ''' </summary>
    ''' <param name="MysqlEXEPath">
    ''' Mysqldump.exe文件路径（不包含可执行文件本身）。
    ''' 注：如果配置了 Mysql.exe环境变量时可设置为空
    ''' </param>
    ''' <param name="serverUrl">服务器地址</param>
    ''' <param name="User">登陆服务器的名称</param>
    ''' <param name="Pwd">登陆服务器使用的密码</param>
    ''' <param name="DatabaseName">需要还原的数据库</param>
    ''' <param name="TabName">需要还原的表单名称列表（不填表示所有表）</param>
    ''' <param name="BackupTabName">备份文件路径（含文件名）</param>
    ''' <param name="Sql"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function BackupTabEx(ByVal MysqlEXEPath As String, ByVal serverUrl As String, ByVal User As String, ByVal Pwd As String, _
                                ByVal DatabaseName As String, ByVal TabName As String, ByVal BackupTabName As String, Optional Sql As String = "") As Object
        Try
            Dim MySqlCmd As String
            serverUrl = " -h" & serverUrl
            User = " -u" & User
            Pwd = " -p" & Pwd
            If Right$(MysqlEXEPath, 1) <> "\" Then MysqlEXEPath = MysqlEXEPath & "\"
            If Len(Sql) > 0 Then Sql = " WHERE " & Sql
            DatabaseName = " " & DatabaseName
            '例子：mysql 数据库名称  -u用户  -p密码  -e  "select   *   from   test.table2 "   >   /home/test.xls
            MySqlCmd = "cmd /c " & MysqlEXEPath & "mysql" & DatabaseName & serverUrl & User & Pwd & " -e "" select   *   from " & _
                       DatabaseName & "." & TabName & Sql & """ > " & BackupTabName
            Call Shell(MySqlCmd, vbHide)  '使用Shell函数调用CMD后台执行mysqldump命令
            Return True
        Catch ex As MySqlException
            Return ex
        End Try
    End Function
    ''' <summary>
    ''' 备份数据表单
    ''' </summary>
    ''' <param name="myConn"></param>
    ''' <param name="TableName"></param>
    ''' <param name="BackTabName"></param>
    ''' <param name="ChrCode"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function BackupTab(ByVal myConn As MySqlConnection, ByVal TableName As String, ByVal BackTabName As String, _
                          Optional ByVal ChrCode As String = "GBK") As Object
        Try
            BackTabName = Replace(BackTabName, "\", "\\")   '加入转义符（因为"\"是转义符）
            Dim oC As MySqlCommand = New MySqlCommand("SELECT * FROM " & TableName & " INTO OUTFILE '" & BackTabName & "' CHARACTER SET " & ChrCode, myConn)
            oC.CommandType = CommandType.Text
            oC.ExecuteNonQuery()
            'oC.Cancel()
            Return True
        Catch ex As MySqlException
            Return ex
        End Try
    End Function
    ''' <summary>
    ''' 导出指定表单数据。
    ''' </summary>
    ''' <param name="myConn">一个已连接到数据的连接对象</param>
    ''' <param name="BackTabName">导出文件名及路径</param>
    ''' <param name="TableName">表单名称</param>
    ''' <param name="FieldList">要显示的字段列表名称</param>
    ''' <param name="SqlSuuree">SQL查询条件</param>
    ''' <param name="OrderField">排序字段</param>
    ''' <param name="ASC">升序排列</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DataExport(ByVal myConn As MySqlConnection, ByVal BackTabName As String, ByVal TableName As String, _
                           Optional ByVal FieldList As String = "*", Optional ByVal SqlSuuree As String = "", Optional ByVal OrderField As String = "", _
                           Optional ByVal ASC As Boolean = True) As Object
        Try
            Dim DataStr As String = "", FieldName As String = ""
            Dim Sql As String
            Dim Order As String = ""
            Dim orderASC As String
            Dim index As Integer
            If Len(SqlSuuree) > 0 Then SqlSuuree = " WHERE " & SqlSuuree
            If ASC = True Then orderASC = " ASC" Else orderASC = " desc"
            If Len(OrderField) > 0 Then Order = " order by " & OrderField & orderASC
            Sql = "SELECT " & FieldList & " FROM " & TableName & SqlSuuree & Order
            Dim oC As MySqlCommand = New MySqlCommand(Sql, myConn)
            oC.CommandType = CommandType.Text
            Dim dataReader As MySqlDataReader = oC.ExecuteReader()

            For index = 0 To dataReader.FieldCount - 1
                If Len(FieldName) = 0 Then
                    FieldName = dataReader.GetName(index)      '格式化名称"
                Else
                    FieldName = FieldName & vbTab & dataReader.GetName(index)      '格式化名称"
                End If
            Next
            If dataReader.HasRows Then
                Do While dataReader.Read()   '一次读取一行
                    For index = 0 To dataReader.FieldCount - 1   '列
                        DataStr &= dataReader.Item(index).ToString & vbTab
                    Next
                    DataStr &= vbCrLf
                Loop
                DataStr = Replace(DataStr, "\", "\\")       '替换"\"为"\\",因为在MySQL中"\"为转义符
                dataReader.Close()
            End If
            Dim fileNumber As Integer = FreeFile()
            FileOpen(fileNumber, BackTabName, OpenMode.Binary, OpenAccess.Write)
            FilePut(fileNumber, DataStr)
            FileClose(fileNumber)
            Return True
        Catch ex As MySqlException
            Return ex
        End Try
    End Function
    ''' <summary>
    ''' 导入数据库表单
    ''' </summary>
    ''' <param name="myConn">已经建立连接的数据库连接对象</param>
    ''' <param name="TableName">要导出的表单名称</param>
    ''' <param name="BackTabName">导入的文件名（路径和文件名不能含有中文字符。）</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ReductionTab(ByVal myConn As MySqlConnection, ByVal TableName As String, ByVal BackTabName As String) As Object
        Try
            BackTabName = Replace(BackTabName, "\", "\\")   '加入转义符（因为"\"是转义符）
            Dim sql As String = "LOAD DATA INFILE '" & BackTabName & "'INTO TABLE " & TableName
            Dim oC As MySqlCommand = New MySqlCommand(sql, myConn)
            oC.CommandType = CommandType.Text
            oC.ExecuteNonQuery()
            'oC.Cancel()
            Return True
        Catch ex As MySqlException
            Return ex
        End Try

    End Function
    ''' <summary>
    ''' 打开表单
    ''' </summary>
    ''' <param name="myConn"></param>
    ''' <param name="TabName">表单名称</param>
    ''' <param name="SqlSuuree">SQL数据查询条件</param>
    ''' <param name="OrderField">排序字段。注：排序字段如果是属于字符型或日期型的话需要把字段名称上加单引号来表示是字符型</param>
    ''' <param name="ASC">升序</param>
    ''' <returns>成功返回<link>MySqlDataReader</link>对象，失败返回MySqlException错误对象</returns>
    ''' <remarks></remarks>
    Public Function OpenTabe(ByVal myConn As MySqlConnection, ByVal TabName As String, Optional ByVal SqlSuuree As String = "", Optional ByVal OrderField As String = "", Optional ByVal ASC As Boolean = True) As Object
        Return OpenTabe(myConn, TabName, , SqlSuuree, OrderField, ASC)
    End Function
    ''' <summary>
    ''' 打开表单
    ''' </summary>
    ''' <param name="myConn"></param>
    ''' <param name="TabName">表单名称</param>
    ''' <param name="FieldList">需要获取的字段</param>
    ''' <param name="SqlSuuree">SQL数据查询条件</param>
    ''' <param name="OrderField">排序字段。注：排序字段如果是属于字符型或日期型的话需要把字段名称上加单引号来表示是字符型</param>
    ''' <param name="ASC">升序</param>
    ''' <returns>成功返回<link>MySqlDataReader</link>对象，失败返回MySqlException错误对象</returns>
    ''' <remarks></remarks>
    Public Function OpenTabe(ByVal myConn As MySqlConnection, ByVal TabName As String, Optional ByVal FieldList As String = "*", Optional ByVal SqlSuuree As String = "", Optional ByVal OrderField As String = "", Optional ByVal ASC As Boolean = True) As Object
        Dim Order As String = ""
        Dim Sql As String
        Dim orderASC As String
        Dim oC As MySqlCommand = Nothing

        If ASC = True Then orderASC = " ASC" Else orderASC = " desc"
        If OrderField <> "" Then Order = " order by " & OrderField & orderASC

        If Len(SqlSuuree) > 0 Then
            Sql = "select " & FieldList & " from " & TabName & " WHERE " & SqlSuuree & " " & Order
        Else
            Sql = "select  " & FieldList & "  from " & TabName & " " & Order
        End If
        Try
            oC = New MySqlCommand(Sql, myConn)
            oC.CommandType = CommandType.Text
            Dim dataReader As MySqlDataReader = oC.ExecuteReader()
            Return dataReader
        Catch ex As MySqlException
            Return ex
        End Try
    End Function
    ''' <summary>
    ''' 打开表单(指定字段不重复值)。
    ''' </summary>
    ''' <param name="myConn"></param>
    ''' <param name="TabName">表单名称</param>
    ''' <param name="NoRepeatField">不重复字段名</param>
    ''' <param name="WhereClause">条件子句</param>
    ''' <param name="FieldList">需要显示的字段名称（多个名称之间用","分隔）</param>
    ''' <returns>成功返回MySqlDataReade对象，失败返回MySqlException错误对象</returns>
    ''' <remarks></remarks>
    Function OpenTabe(ByVal myConn As MySqlConnection, ByVal TabName As String, ByVal NoRepeatField As String, Optional ByVal WhereClause As String = "", Optional ByVal FieldList As String = "*") As Object
        Dim Sql As String
        Try
            If Len(WhereClause) > 0 Then WhereClause = " WHERE " & WhereClause
            Sql = "Select " & FieldList & " From " & TabName & WhereClause & "  GROUP BY " & NoRepeatField
            'select *, count(distinct BankID) from ghsgl group by BankID   '写法1（会多出一列）
            'select * from ghsgl group by BankID        ’写法2
            Dim oC As MySqlCommand = New MySqlCommand(Sql, myConn)
            oC.CommandType = CommandType.Text
            Dim dataReader As MySqlDataReader = oC.ExecuteReader()
            Return dataReader
        Catch ex As MySqlException
            Return ex
        End Try
    End Function
    ''' <summary>
    ''' 指定字段的内容是否存在
    ''' </summary>
    ''' <param name="myConn"></param>
    ''' <param name="TabName">要查询的表单</param>
    ''' <param name="fieldName">要查询的字段</param>
    ''' <param name="val">需要查询的字段值</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function isField(ByVal myConn As MySqlConnection, ByVal TabName As String, ByVal fieldName As String, ByVal val As Object) As Boolean
        Dim Sql As String
        Dim WhereClause As String
        If IsNumeric(val.GetType) Then
            WhereClause = " WHERE " & fieldName & "=" & val
        Else
            WhereClause = " WHERE " & fieldName & "='" & val & "'"
        End If
        Sql = "Select count(*) From " & TabName & WhereClause
        Dim oC As MySqlCommand = New MySqlCommand(Sql, myConn)
        oC.CommandType = CommandType.Text
        Dim dataReader As MySqlDataReader = oC.ExecuteReader()
        Dim count As Integer
        If dataReader.Read Then
            count = Convert.ToInt32(dataReader(0).ToString)
        Else
            count = 0
        End If
        dataReader.Close()
        'oC.Cancel()
        Return IIf(count > 0, True, False)
    End Function
    ''' <summary>
    ''' 指定条件的内容是否存在
    ''' </summary>
    ''' <param name="myConn"></param>
    ''' <param name="TabName">要查询的表单</param>
    ''' <param name="whereClause">条件字段</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function isField(ByVal myConn As MySqlConnection, ByVal TabName As String, ByVal whereClause As String) As Boolean
        If Len(whereClause) > 0 Then
            whereClause = " WHERE " & whereClause
            Dim Sql As String = "Select count(*) From " & TabName & whereClause
            Sql = "Select count(*) From " & TabName & whereClause
            Dim oC As MySqlCommand = New MySqlCommand(Sql, myConn)
            oC.CommandType = CommandType.Text
            Dim dataReader As MySqlDataReader = oC.ExecuteReader()
            Dim count As Integer
            If dataReader.Read Then
                count = Convert.ToInt32(dataReader(0).ToString)
            Else
                count = 0
            End If
            dataReader.Close()
            'oC.Cancel()
            Return IIf(count > 0, True, False)
        End If
        Return False
    End Function
    ''' <summary>
    ''' 插入数据
    ''' </summary>
    ''' <param name="myConn"></param>
    ''' <param name="TabName">需要插入数据的表单</param>
    ''' <param name="FieldList">需要插入数据的字段</param>
    ''' <param name="Values">要插入的数据列表</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SqlInsert(ByVal myConn As MySqlConnection, ByVal TabName As String, ByVal FieldList As Object, ParamArray Values() As Object) As Object
        '------------------------------------------------
        Dim Sql As String = "", Fieldstr As String, BITs() As Byte
        Dim i As Long
        Try
            If IsArray(FieldList) Then Fieldstr = Join(FieldList, ",") Else Fieldstr = FieldList
            If UBound(Values) = 0 And VarType(Values(0)) = (vbVariant Or vbArray) Then    '这里用于处理函数最后一个形参以变体数组赋值的问题
                Values = Values(0)
            End If
            For i = 0 To UBound(Values)
                If Len(Sql) = 0 Then
                    If Not Values(i) Is Nothing Then
                        If VarType(Values(i)) = vbDate Or VarType(Values(i)) = vbString Then
                            Sql = "'" & Values(i) & "'"
                        ElseIf VarType(Values(i)) = vbByte + vbArray Then    '字节数组
                            BITs = Values(i)
                            Sql = "0x" & ToHexString(BITs)    '将二进制数字转为16进制字符串
                        Else
                            Sql = Values(i)
                        End If
                    Else
                        Sql = "null"
                    End If
                Else
                    If Not Values(i) Is Nothing Then
                        If VarType(Values(i)) = vbDate Or VarType(Values(i)) = vbString Then
                            Sql &= ",'" & Values(i) & "'"
                        ElseIf (VarType(Values(i)) = vbByte + vbArray) Then       '字节数组
                            BITs = Values(i)
                            Sql &= ",0x" & ToHexString(BITs)    '将二进制数字转为16进制字符串
                        Else
                            Sql &= "," & Values(i)
                        End If
                    Else
                        Sql &= ",null"
                    End If
                End If
            Next

            If Len(Fieldstr) > 0 Then
                Sql = "INSERT INTO " & TabName & "(" & Fieldstr & ")VALUES(" & Sql & ")"
            Else
                Sql = "INSERT INTO " & TabName & "VALUES(" & Sql & ")"
            End If
            'INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
            Dim oC As MySqlCommand = New MySqlCommand(Sql, myConn)
            oC.CommandType = CommandType.Text
            oC.ExecuteNonQuery()
            'oC.Cancel()
            Return True
        Catch ex As MySqlException
            Return ex
        End Try
    End Function
    ''' <summary>
    ''' 修改表单数据
    ''' </summary>
    ''' <param name="myConn">已连接的数据库对象</param>
    ''' <param name="TabName">表单名称</param>
    ''' <param name="WhereClause">查询条件</param>
    ''' <param name="FieldList">需要修改的字段</param>
    ''' <param name="Values">需要修改的值（与字段一一对应）</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SqlUpdate(ByVal myConn As MySqlConnection, ByVal TabName As String, ByVal WhereClause As String, ByVal FieldList As Object, ParamArray Values() As Object) As Object
        Dim Sql As String = "", FieldArray As Array, BITs() As Byte
        Try
            If IsArray(FieldList) Then FieldArray = FieldList Else FieldArray = Split(FieldList, ",")
            If UBound(Values) = 0 And VarType(Values(0)) = (vbVariant Or vbArray) Then    '这里用于处理函数最后一个形参以变体数组赋值的问题
                Values = Values(0)
            End If
            Dim setField As String = Nothing
            Dim val As String
            For i As Int32 = 0 To FieldArray.Length - 1
                If Values.Length > i Then
                    If Not Values(i) Is Nothing Then
                        If VarType(Values(i)) = vbDate Or VarType(Values(i)) = vbString Then
                            val = "'" & Values(i) & "'"
                        ElseIf VarType(Values(i)) = vbByte + vbArray Then    '字节数组
                            BITs = Values(i)
                            val = "0x" & ToHexString(BITs)    '将二进制数字转为16进制字符串
                        Else
                            val = Values(i)
                        End If
                    Else
                        val = "null"
                    End If
                    If i = 0 Then
                        setField = FieldArray(i) & "=" & val
                    Else
                        setField &= "," & FieldArray(i) & "=" & val
                    End If
                End If
            Next
            Sql = "UPDATE " & TabName & " SET " & setField & " WHERE " & WhereClause
            Dim oC As MySqlCommand = New MySqlCommand(Sql, myConn)
            oC.CommandType = CommandType.Text
            oC.ExecuteNonQuery()
            'oC.Cancel()
            Return True
        Catch ex As MySqlException
            Return ex
        End Try
    End Function
    ''' <summary>
    ''' 删除数据库表单
    ''' </summary>
    ''' <param name="myConn"></param>
    ''' <param name="TableName">表单</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function DellTable(ByVal myConn As MySqlConnection, ByVal TableName As String) As Object
        Try
            Dim sql As String = "DROP TABLE `" & TableName & "`"
            Dim oC As MySqlCommand = New MySqlCommand(sql, myConn)
            oC.CommandType = CommandType.Text
            oC.ExecuteNonQuery()
            'oC.Cancel()
            Return True
        Catch ex As MySqlException
            Return ex
        End Try

    End Function
    ''' <summary>
    ''' 删除指定数据
    ''' </summary>
    ''' <param name="myConn"></param>
    ''' <param name="TabName">表单</param>
    ''' <param name="FindCriteria">查找条件</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function SqlDelete(ByVal myConn As MySqlConnection, ByVal TabName As String, ByVal FindCriteria As String) As Object
        Try
            Dim sql As String = "DELETE FROM " & TabName & " WHERE " & FindCriteria
            Dim oC As MySqlCommand = New MySqlCommand(sql, myConn)
            oC.CommandType = CommandType.Text
            oC.ExecuteNonQuery()
            'oC.Cancel()
            Return True
        Catch ex As MySqlException
            Return ex
        End Try
    End Function
    ''' <summary>
    ''' 锁定指定表单
    ''' </summary>
    ''' <param name="myConn"></param>
    ''' <param name="TabName">需要锁定的表单</param>
    ''' <param name="readLock">读取锁定。<para>true读锁定，false 写锁定</para></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function LockTable(ByVal myConn As MySqlConnection, ByVal TabName As String, Optional ByVal readLock As Boolean = True) As Object
        Try
            Dim sql As String = "LOCK TABLE " & TabName & IIf(readLock, " READ", " WRITE")
            Dim oC As MySqlCommand = New MySqlCommand(sql, myConn)
            oC.CommandType = CommandType.Text
            oC.ExecuteNonQuery()
            'oC.Cancel()
            Return True
        Catch ex As MySqlException
            Return ex
        End Try
    End Function
    ''' <summary>
    ''' 释放全部锁
    ''' </summary>
    ''' <param name="myConn"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UnLockAll(ByVal myConn As MySqlConnection)
        Try
            Dim oC As MySqlCommand = New MySqlCommand("UNLOCK TABLES", myConn)
            oC.CommandType = CommandType.Text
            oC.ExecuteNonQuery()
            'oC.Cancel()
            Return True
        Catch ex As MySqlException
            Return ex
        End Try

    End Function
    ''' <summary>
    ''' 字节数组装16进制字符串
    ''' </summary>
    ''' <param name="bytes">字节数组</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ToHexString(ByVal bytes() As Byte) As String
        Dim builder As StringBuilder = New StringBuilder()
        For Each b In bytes
            builder.Append(b.ToString("X2"))
        Next
        Return builder.ToString().Trim()
    End Function

    ''' <summary>
    ''' 类型是数字类型
    ''' </summary>
    ''' <param name="dataType">类型</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function IsNumeric(ByVal dataType As Type) As Boolean
        If Microsoft.VisualBasic.IsNothing(dataType) Then
            Return False
        Else
            Return dataType.Equals(GetType(Byte)) Or dataType.Equals(GetType(SByte)) Or dataType.Equals(GetType(Boolean)) Or _
                   dataType.Equals(GetType(Short)) Or dataType.Equals(GetType(UShort)) Or dataType.Equals(GetType(Integer)) Or _
                   dataType.Equals(GetType(UInteger)) Or dataType.Equals(GetType(Long)) Or dataType.Equals(GetType(ULong)) Or _
                   dataType.Equals(GetType(Single)) Or dataType.Equals(GetType(Double)) Or dataType.Equals(GetType(Int16)) Or _
                   dataType.Equals(GetType(Int32)) Or dataType.Equals(GetType(Int64)) Or dataType.Equals(GetType(UInt16)) Or _
                   dataType.Equals(GetType(UInt32)) Or dataType.Equals(GetType(UInt64)) Or dataType.Equals(GetType(UIntPtr))
        End If
    End Function
End Module
